import xlrd
import xlwt
import time
import os
from django.db import models
from django.conf import settings
from rest_framework import serializers


class StudentDataManager(models.Manager):
    def save_excel_data(self,file):
        excel_inst = xlrd.open_workbook(filename=None,file_contents=file.read())
        table = excel_inst.sheets()[0]
        rows_length = table.nrows
        # 记录导入失败的行数
        fail_rows = []
        for i in range(1,rows_length):
            rowValues = table.row_values(i)
            try:
                stu_info = self.create(
                    stuid=rowValues[1],
                    sfzid=rowValues[2],
                    name=rowValues[3],
                    college=rowValues[4],
                    major=rowValues[5]
                )
                stu_info.save()
            except:
                fail_rows.append(str(i))
                continue
        # 总数据量、失败数据行、成功数、失败数
        fail_cou = len(fail_rows)
        return rows_length,fail_rows,rows_length-fail_cou,fail_cou

    def output_excel(self):
        filename = f"比对结果.xls"
        workbook = xlwt.Workbook()
        sheet = workbook.add_sheet('比对结果')
        # 读出数据
        data = StuSerializer(instance=self.all(),many=True).data
        # 写入表头
        ths = ["学号","身份证号","姓名","学院","专业","比对结果"]
        for i in range(len(ths)):
            sheet.write(0,i,ths[i])
        for i in range(len(data)):
            item = data[i]
            values = list(item.values())
            for j in range(0,len(values)):
                sheet.write(
                    i+1,j,values[j]
                )
        BASE_DIR = settings.BASE_DIR
        real_path = os.path.join(BASE_DIR,filename)
        workbook.save(real_path)
        return real_path

    def input_exam_img_path(self,files:list):
        file_name_dict = {}
        for file in files:
            sfzid = file.name.split('.')[0]
            file_name_dict[sfzid] = file 
        students = self.filter(sfzid__in=file_name_dict.keys())
        for stu in students:
            stu.exam_img = file_name_dict[stu.sfzid]
            stu.save()

class StudentData(models.Model):
    stuid = models.CharField(max_length=20,verbose_name="学号",db_index=True,unique=True)
    sfzid = models.CharField(max_length=18,verbose_name="身份证号",db_index=True,unique=True)
    name = models.CharField(max_length=20,verbose_name="姓名")
    college = models.CharField(max_length=32,verbose_name="学院")
    major = models.CharField(max_length=64,verbose_name="专业")
    exam_img = models.ImageField(verbose_name="高考照片",null=True)
    sfz_img = models.ImageField(verbose_name="身份证照片",null=True)
    photo = models.ImageField(verbose_name="真人照片",null=True)
    result = models.FloatField(default=0.00,verbose_name="比对结果")

    objects = StudentDataManager()


class StuSerializer(serializers.ModelSerializer):
    class Meta:
        model = StudentData
        fields="__all__"